package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Hashtable;

import orange.database.DBIns;

public class Searchconn  {
	private static final Hashtable<String, String> Cache = new Hashtable<String, String>();//缓存每天更新一次
	private String value;
	public Searchconn(int i){
		super();
		String[] view_list = SQLparams.views_sql;
		try {
			//准备缓存读取数据库第二次响应速度会提升
			SimpleDateFormat time = new SimpleDateFormat("yyyyMMdd");
			String key = time.format(new Date())+i;
			value = Cache.get(key);
			if(value==null){
				value=Querytablename(view_list[i]);
				Cache.put(key, value);
			}else{
//				System.out.println(key+"缓存命中");
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			value="";
		}
	}
	public Searchconn(){
		
	}
	//重写getValue方法
	public String getValue(){
		return value;
	}
	public static void main(String[] args){
		for(int i=0;i<17;i++){
		Searchconn sc=new Searchconn(i);
		System.out.println(sc.getValue());
		}
	}
	// table表名查询该表所有数据
	public String Querytablename(String sql){
//		String sql = "select * from " + tablename + " order by _VALUE asc";
//		System.out.println("sql: "+sql);
		ResultSet rs = null;
		StringBuffer buffer1 = new StringBuffer();
	
		Connection conn = DBIns.getInstance().getConnection();
		try {
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			// for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
			// System.out.println(rs.getMetaData().getColumnName(i));

			rs.next();
			// 判断show与value是否相等 如果不相等 将value加到 show字段签名并用括号表示
			if (String.valueOf(rs.getString("value")).trim().equals(
					String.valueOf(rs.getString("show")).trim())) {
				buffer1.append("<option value=\""
						+ String.valueOf(rs.getString("value")).trim()
						+ "\" selected=\"selected\">" 
						+ String.valueOf(rs.getString("show")).trim()
						+ "</option>");
				rs.next();
				do {
					String temp = String.valueOf(rs.getString("value")).trim();
					if (temp.contains("全国")||temp.contains("地区")) {
						continue;
					}
					buffer1.append("<option value=\""
							+ String.valueOf(rs.getString("value")).trim() + "\">"
							+ String.valueOf(rs.getString("show")).trim()
							+ "</option>");
				} while (rs.next());
			} else {
				buffer1.append("<option value=\""
						+ String.valueOf(rs.getString("value")).trim()
						+ "\" selected=\"selected\">" + "("
						+ String.valueOf(rs.getString("value")).trim() + ")"
						+ String.valueOf(rs.getString("show")).trim()
						+ "</option>");
				while (rs.next()) {
					buffer1.append("<option value=\""
							+ String.valueOf(rs.getString("value")).trim() + "\">"
							+ "(" + String.valueOf(rs.getString("value")).trim()
							+ ")" + String.valueOf(rs.getString("show")).trim()
							+ "</option>");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		

		// System.out.println(System.currentTimeMillis()-time);//测试查询时间
		String value = new String(buffer1);
		// System.out.println(value);
		// }
//		Close(rs);// 关闭数据库连接
//		rs.close();
		DBIns.getInstance().release(conn);
		// }
		return value;
	}
	
	// 获取数据表记录总数
	public String getCountBytablename(String table) {
		String[] tablenames = { "orange_z_mianji", "orange_z_jinliang",
				"orange_z_chuliang", "orange_z_jinchuliang",
				"orange_z_chanliang", "orange_z_cbsy",
				"orange_w_changliangandmianji", "orang_w_jinchumaoyi" };
		
		SimpleDateFormat time = new SimpleDateFormat("yyyyMMdd");
		String key = time.format(new Date())+table;
		String rowCount =Cache.get(key);
		if(rowCount==null){
			String sql = null;
			Connection conn = DBIns.getInstance().getConnection();
			try {
				sql = "select count(*) as totalCount from " + table;
				ResultSet rs;
				Statement stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);

				if (rs.next()) {
					rowCount = rs.getString("totalCount");
					// 获取记录总数
				}
//				rs.close();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			DBIns.getInstance().release(conn);
			if(rowCount!=null)
			Cache.put(key, rowCount);
		}else{
//			System.out.println(key+"缓存命中");
		}
		
		
		// Close(rs);
		return rowCount;
	}

}
